// 导入模块
const mysql = require('mysql2/promise');
const dataBase = require('../config/index.js');

// 创建连接池
const pool = mysql.createPool({
  host: dataBase.HOST,
  user: dataBase.USERNAME,
  password: dataBase.PASSWORD,
  database: dataBase.DATABASE,
  port: dataBase.PORT
});

// mysql的连接
const allServices = {
  async query(sql, values) {
    try {
      const conn = await pool.getConnection();  // 等待连接池连接
      const [rows, fields] = await conn.query(sql, values); // 执行sql语句
      pool.releaseConnection(conn); // 释放连接
      
      return Promise.resolve(rows);
    } catch (error) {
      return Promise.reject(error);
    }

  }
}

// 登录
const userLogin = (username,password) => {
    let _sql = `SELECT * FROM users WHERE username='${username}' AND password='${password}';`
    return allServices.query(_sql) 
  }

// 查找碳纤维车架
const getAllFrameList = ({page, size}) => {
  let _sql = `SELECT * 
              FROM bikeframe 
              ORDER BY id DESC
              LIMIT ${size} OFFSET ${(page - 1) * size};`
  return allServices.query(_sql)
}

// 查找铝合金车架
const getAllFrame2List = ({page, size}) => {
  let _sql = `SELECT * 
              FROM bikeframe2
              ORDER BY id DESC
              LIMIT ${size} OFFSET ${(page - 1) * size};`
  return allServices.query(_sql)
}

// 碳车架总数量
const getAllFrameCount = () => {
  let _sql = `SELECT COUNT(*) AS count FROM bikeframe;`
  return allServices.query(_sql)
}

// 铝合金车架总数量
const getAllFrame2Count = () => {
  let _sql = `SELECT COUNT(*) AS count FROM bikeframe2;`
  return allServices.query(_sql)
}

// 获取用户信息
const getUserInfo = (userId) => {
  let _sql = `SELECT * FROM users WHERE id=${userId};`
  return allServices.query(_sql)
}

// 添加到购物车
const addToCart = (userId, frameId, frameType) => {
  const sql = `INSERT INTO cart (user_id, frame_id, frame_type) VALUES (?, ?, ?)`;
  query(sql, [userId, frameId, frameType]);
}

// 获取购物车列表
const getCartList = async (userId) => {
  const _sql = `SELECT * FROM cart WHERE user_id = ?`;
  return allServices.query(_sql, [userId]);
}

// 从购物车中删除商品
const removeFromCart = async (cartId, userId) => {
  const _sql = 'DELETE FROM cart WHERE id = ? AND user_id = ?'
  return allServices.query(_sql) 
}

// 获取购物车商品数量
const getCartCount = async (userId) => {
  const _sql = 'SELECT COUNT(*) as count FROM cart WHERE user_id = ?'
  return allServices.query(_sql) 
}

module.exports = {
    userLogin,
    getAllFrameList,
    getAllFrame2List,
    getAllFrameCount,
    getAllFrame2Count,
    getUserInfo,
    addToCart,
    getCartList,
    removeFromCart,
    getCartCount
}